package gov.nhso.prov.acc.apps.acc6;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;

import gov.nhso.prov.acc.beans.Acc6ExpenseBean;
import gov.nhso.prov.acc.beans.Acc6ExpenseTransferBean;
import gov.nhso.prov.acc.beans.Acc6TransactionBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings("rawtypes")
public class ACC60304 extends Utility{
	public String url = "/apps/acc6/ACC60304A.jsp";

	public ACC60304(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if(job.equals("init")){
			this.clearLastRequest(request, "lastSearch");
			this.init(request);
		}else if(job.equals("search")){
			this.setLastRequest(request, "lastSearch");
			this.search(request);
		}else if(job.equals("view")){
			this.view(request);
		}else if(job.equals("back")){
			if (this.getLastRequest(request, "lastSearch")) {
				this.search(request);
			} else {
				this.init(request);
			}
		}else if(job.equals("rollBack")){
			this.rollBack(request);
		}else if(job.equals("save")){
			this.save(request);
		}
	}
	
	private String init(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("statusExpense", this.properties().statusExpense());
			request.setAttribute("expenseStatusSel", "A");
			request.setAttribute("docNo", "");
			request.setAttribute("job", "search");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			PageBean pageBean = new PageBean();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String docNo = this.getAttr(request, "docNo");
			String refNo = this.getAttr(request, "refNo");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			String expenseStatus = this.getAttr(request, "expenseStatus");
			String expenseSubject = this.getAttr(request, "expenseSubject");
			String condition = "";

			this.beanUtil().copyRequest2Bean(request, pageBean);
			expenseBean.setDocNo(docNo);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setExpenseSubject(expenseSubject);
			expenseBean.setExpenseStatus(expenseStatus);
			expenseBean.setActive("Y");
			expenseBean.setExpenseType("T");
			expenseBean.setBudgetYear(budgetYear);
			if (startDate != null && !startDate.equals("")){
				startDate = this.dateUtil().changeFormatDate(startDate, "/", "/");
				condition += " AND TRUNC(EXPENSE_DATE) >= TO_DATE('"+startDate+"','DD/MM/YYYY') ";
			}
			if (endDate != null && !endDate.equals("")){
				endDate = this.dateUtil().changeFormatDate(endDate, "/", "/");
				condition += " AND TRUNC(EXPENSE_DATE) <= TO_DATE('"+endDate+"','DD/MM/YYYY') ";
			}
			if (refNo != null && !refNo.equals("")) {
				condition += " AND DOC_NO IN (SELECT DOC_NO FROM ACC6_EXPENSE_TRANSFER WHERE REF_NO LIKE '%" + refNo + "%') ";
			}
			pageBean = dao.selectPage(expenseBean, " " + condition,pageBean);
			
			if (pageBean.getPageData() == null) {
				this.message(request).setMsgCode("I001");
			}
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("pageBean", pageBean);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("startDate", this.getAttr(request, "startDate"));
			request.setAttribute("endDate", this.getAttr(request, "endDate"));
			request.setAttribute("docNo", docNo);
			request.setAttribute("job", "search");
			request.setAttribute("expenseStatusSel", expenseStatus);
			request.setAttribute("statusExpense", this.properties().statusExpense());
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60304A.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String view(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String expenseDate = "";
			String currentDate = this.dateUtil().getCurrentDate().replaceAll("-", "");
			String flag = "Y";
			List listExpenseTransfer = new ArrayList();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			
			expenseBean = new Acc6ExpenseBean();
			expenseBean.setDocNo(docNo);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean = (Acc6ExpenseBean) dao.select(expenseBean);
			
			StringBuffer sb = new StringBuffer();
			Map pv = new HashMap();
			expenseTransferBean.setDocNo(docNo);
			expenseTransferBean.setActive("Y");
			Map<String, String> xcols = new HashMap<String, String>();
			xcols.put("TRANSACTION_TIMESTAMP", "TO_CHAR(TRANSACTION_TIMESTAMP, 'YYYYMMDDHH24MISSFF3')");
			listExpenseTransfer = dao.selectList(expenseTransferBean, xcols, " ORDER BY TRANSFER_SEQ ASC");
			
			for (int i = 0; i < listExpenseTransfer.size(); i++) {
				Acc6ExpenseTransferBean bean = (Acc6ExpenseTransferBean) listExpenseTransfer.get(i);
				sb.delete(0, sb.length());
				sb.append("SELECT CASE WHEN LENGTH(ALLOCATE_NAME) < 21 THEN ALLOCATE_NAME ELSE RPAD(SUBSTR(ALLOCATE_NAME,0,20),23,'.')  END AS ALLOCATE_NAME_SUB,ALLOCATE_NAME ");
				sb.append("FROM "+this.schema+".ACC6_ALLOCATE ");
				sb.append("WHERE ALLOCATE_LEVEL >= 0 AND ALLOCATE_APPLY = 'Y' AND ALLOCATE_CODE='"+bean.getAllocateCode()+"'");
				pv = dao.findDataSql(sb.toString());
				if (pv.get("ALLOCATE_NAME") == null){
					bean.setAllocateName("");
				}else{
					bean.setAllocateName(pv.get("ALLOCATE_NAME").toString());
				}
				if (pv.get("ALLOCATE_NAME_SUB") == null){
					bean.setAllocateSub("");
				}else{
					bean.setAllocateSub(pv.get("ALLOCATE_NAME_SUB").toString());
				}
				sb.delete(0, sb.length());
				pv = new HashMap();
				sb.append("SELECT CASE WHEN LENGTH(VENDER_NAME) < 21 THEN VENDER_NAME ELSE RPAD(SUBSTR(VENDER_NAME,0,20),23,'.')  END AS VENDER_NAME_SUB,VENDER_NAME,VENDER_ACCOUNT ");
				sb.append("FROM "+this.schema+".ACC6_VENDER ");
				sb.append("WHERE VENDER_CODE='"+bean.getVenderCode()+"'");
				pv = dao.findDataSql(sb.toString());
				
				if (pv.get("VENDER_NAME") == null){
					bean.setVenderName("");
				}else{
					bean.setVenderName(pv.get("VENDER_NAME").toString());
				}
				if (pv.get("VENDER_NAME_SUB") == null){
					bean.setVenderSub("");
				}else{
					bean.setVenderSub(pv.get("VENDER_NAME_SUB").toString());
				}
				if (pv.get("VENDER_ACCOUNT") == null){
					bean.setVenderAccount("");
				}else{
					bean.setVenderAccount(pv.get("VENDER_ACCOUNT").toString());
				}
			}
			expenseDate = this.dateUtil().changeFormatDateFromDB(expenseBean.getExpenseDate(), ""); 
			if ((Integer.parseInt(expenseDate) - Integer.parseInt(currentDate)) <= 0 && expenseBean.getExpenseStatus().equals("A")) {
				flag = "N";
			}
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("docNo", docNo);
			request.setAttribute("flag", flag);
			request.setAttribute("expenseBean", expenseBean);
			request.setAttribute("listExpenseTransfer", listExpenseTransfer);
			request.setAttribute("job", "save");
			request.setAttribute("now", this.dateUtil().getThaiCurrentDate());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "/apps/acc6/ACC60304B.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String rollBack(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			expenseBean.setDocNo(docNo);
			expenseBean.setBudgetYear(budgetYear);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setExpenseStatus("P");
			expenseBean.setUpdatedBy(this.user(request).getUserId());
			expenseBean.setUpdatedDate(this.sysDate);
			dao.update(expenseBean);

			request.setAttribute("docNo", "");
			request.setAttribute("budgetYear", "");
			request.setAttribute("provinceId", provinceId);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.search(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String save(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String docNo = this.getAttr(request, "docNo");
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			String expenseDate = this.getAttr(request, "expenseDate");
			String ts = "";
			Acc6TransactionBean transactionBean = new Acc6TransactionBean();
			Acc6ExpenseBean expenseBean = new Acc6ExpenseBean();
			Acc6ExpenseTransferBean expenseTransferBean = new Acc6ExpenseTransferBean();
			
			expenseBean.setDocNo(docNo);
			expenseBean.setProvinceId(provinceId);
			expenseBean.setBudgetYear(budgetYear);
			dao.select(expenseBean);
			
			if (expenseBean.getExpenseStatus().equals("A")) {
				expenseBean = new Acc6ExpenseBean();
				String[] refNo = request.getParameterValues("refNo");
				String[] categoryId = request.getParameterValues("categoryId");
				String[] venderCode = request.getParameterValues("venderCode");
				String[] allocateCode = request.getParameterValues("allocateCode");
				String[] transferAmount = request.getParameterValues("transferAmount");
				String[] transferSeq = request.getParameterValues("transferSeq");
				String[] isBorrow = request.getParameterValues("isBorrow");

				for(int i =0;i<refNo.length;i++){
					ts = this.dateUtil().getCurrentTimeStamp2DB();
					transactionBean = new Acc6TransactionBean();
					expenseTransferBean = new Acc6ExpenseTransferBean();
					
					transactionBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
					transactionBean.setTransactionType("T");
					transactionBean.setBudgetYear(budgetYear);
					transactionBean.setProvinceId(provinceId);
					transactionBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
					transactionBean.setRefNo(this.encodeUTF(refNo[i]));
					transactionBean.setCategoryId(categoryId[i]);
					transactionBean.setVenderCode(venderCode[i]);
					transactionBean.setAllocateCode(allocateCode[i]);
					transactionBean.setExpenseAmount(transferAmount[i]);
					transactionBean.setIsBorrow(isBorrow[i]);
					dao.insert(transactionBean);

					expenseTransferBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
					expenseTransferBean.setDocNo(docNo);
					expenseTransferBean.setTransferSeq(transferSeq[i]);
					expenseTransferBean.setUpdatedBy(this.user(request).getUserId());
					expenseTransferBean.setUpdatedDate(this.sysDate);
					dao.update(expenseTransferBean);
				}
				expenseBean.setExpenseDate(this.dateUtil().changeDateToDB(expenseDate));
				expenseBean.setExpenseStatus("W");
				expenseBean.setDocNo(docNo);
				expenseBean.setBudgetYear(budgetYear);
				expenseBean.setProvinceId(provinceId);
				expenseBean.setUpdatedBy(this.user(request).getUserId());
				expenseBean.setUpdatedDate(this.sysDate);
				dao.update(expenseBean);
				
				request.setAttribute("docNo", docNo);
				request.setAttribute("budgetYear", budgetYear);
				request.setAttribute("province", provinceId);
				this.message(request).setMsgCode("I002");
			} else {
				this.message(request).setMsg("E", "ไม่สามารถยืนยันได้ สถานะการจ่ายไม่เป็นรออนุมัติ กรุณาตรวจสอบ");
			}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.view(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
}
